# coding= utf-8

import pymongo
import sys
from dateutil.relativedelta import relativedelta
import time
import os
import xlsxwriter
import calendar
from datetime import datetime
import base64
from urllib.parse import parse_qs, unquote_plus


def modify_image_url(url, suffix="._AC_SR100,100_"):
    # 分离图片文件名和后缀
    base, ext = url.rsplit('.', 1)
    # 拼接新的URL
    new_url = f"{base}{suffix}.{ext}"
    return new_url


def decode_base64(encoded_string):
    # 手动添加等号以补足字符串长度
    padding = 4 - (len(encoded_string) % 4)
    if padding:
        encoded_string += "=" * padding
    # 解码
    decoded_bytes = base64.b64decode(encoded_string)
    # 将字节转换为字符串
    decoded_string = decoded_bytes.decode('utf-8')
    return decoded_string


class Export:
    def __init__(self):
        self.where = unquote_plus(sys.argv[1]).split(',')
        # 连接MongoDB
        self.myclient = pymongo.MongoClient("mongodb://localhost:27017/")
        mydb = self.myclient["xp_analyse"]
        self.my_product = mydb["wt_order_product"]
        self.low_order = mydb["wt_low_order"]

    def get_intermediate_months(self, start_month, end_month):
        # 解析输入字符串为 datetime 对象
        start_date = datetime.strptime(start_month, '%Y-%m')
        end_date = datetime.strptime(end_month, '%Y-%m')

        # 初始化结果列表
        months = []

        # 生成中间月份
        current_date = start_date
        while current_date <= end_date:
            months.append(current_date.strftime('%Y-%m'))
            current_date += relativedelta(months=1)
        return months

    def get_same_period_last_year(self, months):
        same_period_last_year = []

        for month in months:
            # 将字符串解析为 datetime 对象
            date = datetime.strptime(month, '%Y-%m')
            # 减去一年
            last_year_date = date - relativedelta(years=1)
            # 将 datetime 对象格式化为字符串并添加到结果列表中
            same_period_last_year.append(last_year_date.strftime('%Y-%m'))

        return same_period_last_year

    def get_previous_period(self, months):
        # 计算月份列表的长度
        num_months = len(months)

        # 获取首个月份
        first_month = datetime.strptime(months[0], '%Y-%m')

        # 推前 num_months 个月
        start_date = first_month - relativedelta(months=num_months)

        # 生成前推的月份列表
        previous_period = [(start_date + relativedelta(months=i)).strftime('%Y-%m') for i in range(num_months)]

        return previous_period

    def remove_empty_elements(self, input_list):
        # 使用列表解析过滤掉空元素
        return [element for element in input_list if element]

    def export_xls(self):
        where = {'sj_name': {'$exists': True, '$ne': ''}}
        filter_list = self.remove_empty_elements(self.where)
        for i in filter_list:
            item = i.split('=')
            if item[1] and item[0]:
                if item[0] == 'domainId':
                    where['domain_id'] = int(item[1])
                elif item[0] == 'month_range':
                    months = item[1].split('|')
                    current_month = self.get_intermediate_months(months[0], months[1])  # 当前月份区间
                    last_year_month = self.get_same_period_last_year(current_month)  # 环比月份
                    period_month = self.get_previous_period(current_month)  # 同比月份
                    all_month = current_month + last_year_month + period_month
                    two_month = current_month + last_year_month
                elif item[0] == 'root_cate':
                    where['root_cate'] = decode_base64(item[1])
                elif item[0] == 'avg_num':
                    avg_num = int(item[1])
                elif item[0] == 'exportType':
                    export_type = item[1]

        # 创建文件地址
        dir = time.strftime('%Y-%m', time.localtime(time.time()))
        date = time.strftime('%Y-%m-%d-%H-%M-%S', time.localtime(time.time()))
        dir_path = './upload/' + export_type + '/' + dir + '/'
        if not os.path.exists(dir_path):
            os.makedirs(dir_path)
        path = dir_path + export_type + '-' + date + '.xlsx'
        xls = xlsxwriter.Workbook(path)
        sht1 = xls.add_worksheet('sheet1')
        head_style = xls.add_format(
            {'color': 'white', 'align': 'center', 'bg_color': '#008B45', 'bold': True,
             'font_name': 'Times New Roman, fantasy', 'valign': 'vcenter'})
        body_style = xls.add_format({'valign': 'vcenter', 'align': 'center', 'font_name': 'Times New Roman'})
        sht1.set_row(0, 25)

        if export_type == 'line_analyse':
            where['month'] = {"$in": all_month}
            head = ['数据名(产品线)', '根分类', 'Amazon分类树', 'ASIN量(当前/同比/环比)', '总单量(当前/同比/环比)	', '头部ASIN产品', '组数量',
                    '组单量及占比(当前)', '组单量及占比(同比)', '单量同比百分比', '组单量及占比(环比)']
            sht1.set_column(0, len(head), 25)
            # 定义聚合管道
            pipeline = [
                # 过滤特定月份的数据
                {'$match': where},
                # 根据 sj_name 和 month 进行初步分组
                {
                    '$group': {
                        '_id': {
                            'sj_name': '$sj_name',
                            'month': '$month',
                            'group_name': '$group_name'
                        },
                        'asin_count': {'$addToSet': '$asin'},
                        'domain_id': {'$first': '$domain_id'},
                        'categories_tree': {'$addToSet': '$categories_tree'},  # 使用 addToSet 来累积不同的 categories_tree
                        'root_cate': {'$addToSet': '$root_cate'},
                        'total_order_num': {'$sum': '$order_num'},
                        'items': {
                            '$push': {
                                'asin': '$asin',
                                'order_num': '$order_num',
                            }
                        }
                    }
                },
                # 计算 asin 数量
                {
                    '$addFields': {
                        'asin_count': {'$size': '$asin_count'},
                        'domain_id': '$domain_id'
                    }
                },
                # 进一步分组以计算每个 sj_name 和 month 的数据
                {
                    '$group': {
                        '_id': {
                            'sj_name': '$_id.sj_name',
                            'month': '$_id.month'
                        },
                        'domain_id': {'$first': '$domain_id'},
                        'group_orders': {
                            '$push': {
                                'group_name': '$_id.group_name',
                                'asin_count': '$asin_count',
                                'total_order_num': '$total_order_num',
                                'categories_tree': '$categories_tree',
                                'root_cate': '$root_cate',
                                'items': '$items'
                            }
                        },
                        'categories_tree': {'$first': '$categories_tree'},
                        'root_cate': {'$first': '$root_cate'},
                        'total_order_num': {'$sum': '$total_order_num'},
                        'asin_count': {'$sum': '$asin_count'}
                    }
                },
                # 进一步分组以计算每个 sj_name 的数据
                {
                    '$group': {
                        '_id': '$_id.sj_name',
                        'domain_id': {'$first': '$domain_id'},
                        'data': {
                            '$push': {
                                'month': '$_id.month',
                                'group_orders': '$group_orders',
                                'total_order_num': '$total_order_num',
                                'asin_count': '$asin_count'
                            }
                        },
                        'categories_tree': {'$first': '$categories_tree'},
                        'root_cate': {'$first': '$root_cate'}
                    }
                },
                # 将同比、环比月份的数据分别处理
                {
                    '$addFields': {
                        'current_period': {
                            '$filter': {
                                'input': '$data',
                                'as': 'item',
                                'cond': {'$in': ['$$item.month', current_month]}
                            }
                        },
                        'same_period_last_year': {
                            '$filter': {
                                'input': '$data',
                                'as': 'item',
                                'cond': {'$in': ['$$item.month', last_year_month]}
                            }
                        },
                        'previous_period': {
                            '$filter': {
                                'input': '$data',
                                'as': 'item',
                                'cond': {'$in': ['$$item.month', period_month]}
                            }
                        }
                    }
                },
                # 添加一个阶段来计算 current_period 中 total_order_num 的总和
                {
                    '$addFields': {
                        'current_period_total_order_num': {
                            '$reduce': {
                                'input': '$current_period',
                                'initialValue': 0,
                                'in': {
                                    '$add': ['$$value', '$$this.total_order_num']
                                }
                            }
                        }
                    }
                },
                {
                    '$match': {"current_period_total_order_num": {"$gt": 0}}
                },
                {
                    '$sort': {
                        'current_period_total_order_num': -1
                    }
                }
            ]
            for col, result in enumerate(self.my_product.aggregate(pipeline, allowDiskUse=True)):
                sht1.set_row(col + 1, 50)
                for f_index, f in enumerate(result):
                    if f == '_id':
                        sj_name = result[f]
                        sht1.write(col + 1, 0, sj_name, body_style)
                    if f == 'root_cate':
                        root_cate = '|'.join(result[f])
                        sht1.write(col + 1, 1, root_cate, body_style)
                    if f == 'categories_tree':
                        categories_tree = '|'.join(result[f])
                        sht1.write(col + 1, 2, categories_tree, body_style)

                    # 获取asin量
                    current_asin_count = 0
                    current_order_count = 0
                    same_asin_count = 0
                    same_order_count = 0
                    last_asin_count = 0
                    last_order_count = 0

                    # 当前单量等数据
                    current_group_data = {}
                    group_top_product = {}

                    if len(result.get('current_period', [])) > 0:
                        for item in result['current_period']:
                            current_order_count += item['total_order_num']
                            current_asin_count += item['asin_count']

                            # 组单量及其占比
                            for order in item['group_orders']:
                                if order['group_name'] not in current_group_data:
                                    current_group_data[order['group_name']] = 0
                                current_group_data[order['group_name']] += order['total_order_num']

                                # 获取头部产品(每组一个)
                                if order['group_name'] not in group_top_product:
                                    group_top_product[order['group_name']] = {}
                                for product in order['items']:
                                    if product['asin'] not in group_top_product[order['group_name']]:
                                        group_top_product[order['group_name']][product['asin']] = 0
                                    group_top_product[order['group_name']][product['asin']] += product['order_num']

                    top_product = []
                    for k, v in group_top_product.items():
                        sorted_v = sorted(v.items(), key=lambda x: x[1], reverse=True)
                        first_key, first_value = sorted_v[0]
                        # t_item = k + '-' + first_key + '-' + first_value + '(单)'
                        top_product.append({
                            'group_name': k,
                            'asin': first_key,
                            'order_num': first_value
                        })

                    top_product.sort(key=lambda x: x['order_num'], reverse=True)
                    current_group_data = dict(
                        sorted(current_group_data.items(), key=lambda item: item[1], reverse=True))

                    current_desc = []
                    if current_order_count > 0:
                        for k, v in current_group_data.items():
                            current_desc.append({
                                'name': k,
                                'order_num': v,
                                'percent': f"{v * 100 / current_order_count:.2f}%"
                            })

                    # 同比单量等数据
                    same_group_data = {}
                    if len(result.get('previous_period', [])) > 0:
                        for item in result['previous_period']:
                            same_order_count += item['total_order_num']
                            same_asin_count += item['asin_count']

                            # 组单量及其占比
                            for order in item['group_orders']:
                                if order['group_name'] not in same_group_data:
                                    same_group_data[order['group_name']] = 0
                                same_group_data[order['group_name']] += order['total_order_num']

                    same_group_data = dict(sorted(same_group_data.items(), key=lambda item: item[1], reverse=True))
                    same_desc = []
                    if same_order_count > 0:
                        for k, v in same_group_data.items():
                            same_desc.append({
                                'name': k,
                                'order_num': v,
                                'percent': f"{v * 100 / same_order_count:.2f}%"
                            })

                    # 单量同比百分比
                    same_percent = []
                    for ck, cv in current_group_data.items():
                        if ck in same_group_data and same_group_data[ck] > 0:
                            same_percent.append({
                                'name': ck,
                                'percent': f"{cv * 100 / same_group_data[ck]:.2f}%"
                            })

                    # 环比单量等数据
                    last_group_data = {}
                    if len(result.get('same_period_last_year', [])) > 0:
                        for item in result['same_period_last_year']:
                            last_order_count += item['total_order_num']
                            last_asin_count += item['asin_count']

                            # 组单量及其占比
                            for order in item['group_orders']:
                                if order['group_name'] not in last_group_data:
                                    last_group_data[order['group_name']] = 0
                                last_group_data[order['group_name']] += order['total_order_num']

                    last_group_data = dict(sorted(last_group_data.items(), key=lambda item: item[1], reverse=True))
                    last_desc = []
                    if last_order_count > 0:
                        for k, v in last_group_data.items():
                            last_desc.append({
                                'name': k,
                                'order_num': v,
                                'percent': f"{v * 100 / last_order_count:.2f}%"
                            })
                    # 写入输入
                    sht1.write(col + 1, 3, f"{current_asin_count}/{same_asin_count}/{last_asin_count}", body_style)
                    sht1.write(col + 1, 4, f"{current_order_count}/{same_order_count}/{last_order_count}", body_style)

                    # 头部产品
                    top_product_desc = ''
                    for top in top_product:
                        t_item = top['group_name'] + '-' + top['asin'] + '-' + str(top['order_num']) + '单'
                        top_product_desc += t_item + '|'
                    sht1.write(col + 1, 5, top_product_desc, body_style)

                    # 组数量
                    sht1.write(col + 1, 6, len(current_desc), body_style)

                    # 组单量及占比-当前
                    current_desc_str = ''
                    for desc in current_desc:
                        c_item = desc['name'] + '-' + str(desc['order_num']) + '单-' + str(desc['percent'])
                        current_desc_str += c_item + '|'
                    sht1.write(col + 1, 7, current_desc_str, body_style)

                    # 组单量及占比-同比
                    same_desc_str = ''
                    for same in same_desc:
                        s_item = same['name'] + '-' + str(same['order_num']) + '单-' + str(same['percent'])
                        same_desc_str += s_item + '|'
                    sht1.write(col + 1, 8, same_desc_str, body_style)

                    # 组单量及占比-同比
                    same_percent_str = ''
                    for percent in same_percent:
                        p_item = percent['name'] + str(percent['percent'])
                        same_percent_str += p_item + '|'
                    sht1.write(col + 1, 9, same_percent_str, body_style)

                    # 组单量及占比-环比
                    last_desc_str = ''
                    for last in last_desc:
                        l_item = last['name'] + '-' + str(last['order_num']) + '单-' + str(last['percent'])
                        last_desc_str += l_item + '|'
                    sht1.write(col + 1, 10, last_desc_str, body_style)

            # 添加表头
            for index, h_item in enumerate(head):
                sht1.write(0, index, h_item, head_style)
        elif export_type == 'avg_order':
            where['month'] = {"$in": two_month}
            month_days = {}
            # 遍历月份列表，计算每个月的天数
            for month in two_month:
                # 将月份字符串解析为datetime对象
                date = datetime.strptime(month, '%Y-%m')
                # 获取该月份的天数
                days_in_month = calendar.monthrange(date.year, date.month)[1]
                # 将结果存储在字典中
                month_days[month] = days_in_month
            head = ['ASIN', '图片', '根分类', '数据名(产品线)', 'Amazon分类树', '所属组', '产品线日均单', '日均单量', '环比日均单']
            sht1.set_column(0, len(head), 25)

            # 定义聚合管道
            pipeline = [
                {
                    '$match': where
                },
                {
                    '$sort': {
                        'month': -1
                    }
                },
                {
                    '$group': {
                        '_id': '$asin',
                        'sj_name': {'$first': '$sj_name'},
                        'image': {'$first': '$image'},
                        'domain_id': {'$first': '$domain_id'},
                        'category_id': {'$first': '$category_id'},
                        'group_name': {'$first': '$group_name'},
                        'categories_tree': {'$first': '$categories_tree'},
                        'root_cate': {'$first': '$root_cate'},
                        'items': {
                            '$push': {
                                'month': '$month',
                                'order_num': '$order_num',
                            }
                        },
                    }
                },
                {
                    '$addFields': {
                        'current': {
                            '$filter': {
                                'input': '$items',
                                'as': 'item',
                                'cond': {
                                    '$gte': ['$$item.month', months[0]]
                                }
                            }
                        }
                    }
                },
                {
                    '$addFields': {
                        'last': {
                            '$filter': {
                                'input': '$items',
                                'as': 'item',
                                'cond': {
                                    '$lt': ['$$item.month', months[0]]
                                }
                            }
                        }
                    }
                },
                {
                    '$group': {
                        '_id': '$sj_name',
                        'asin_data': {
                            '$push': {
                                '_id': '$_id',
                                'sj_name': '$sj_name',
                                'image': '$image',
                                'domain_id': '$domain_id',
                                'category_id': '$category_id',
                                'group_name': '$group_name',
                                'categories_tree': '$categories_tree',
                                'root_cate': '$root_cate',
                                'items': '$items',
                                'current': '$current',
                                'last': '$last',
                                'avg_num': '$avg_num',
                            }
                        },
                        'total': {
                            '$push': '$current'
                        }
                    }
                },
                {
                    '$unwind': '$total'
                },
                {
                    '$unwind': '$total'
                },
                {
                    '$group': {
                        '_id': {
                            'sj_name': '$_id',
                            'month': '$total.month'
                        },
                        'order_num_sum': {
                            '$sum': '$total.order_num'
                        },
                        'asin_data': {'$first': '$asin_data'}
                    }
                },
                {
                    '$group': {
                        '_id': '$_id.sj_name',
                        'total': {
                            '$push': {
                                'month': '$_id.month',
                                'order_num': '$order_num_sum'
                            }
                        },
                        'asin_data': {'$first': '$asin_data'}
                    }
                },
                {
                    '$unwind': '$asin_data'
                },
                {
                    '$replaceRoot': {
                        'newRoot': {
                            '$mergeObjects': ['$asin_data', {'average_order': '$total'}]
                        }
                    }
                },
                {
                    '$match': {
                        'current': {
                            '$elemMatch': {
                                'month': months[1],
                                'order_num': {'$gte': avg_num * month_days[months[1]]}
                            },
                        }
                    }
                },
                {
                    '$addFields': {
                        'avg_num': {
                            '$divide': [
                                {
                                    '$max': {
                                        '$map': {
                                            'input': '$items',
                                            'as': 'item',
                                            'in': {
                                                '$cond': [
                                                    {'$eq': ['$$item.month', months[1]]},
                                                    '$$item.order_num',
                                                    None
                                                ]
                                            }
                                        }
                                    }
                                },
                                {'$toInt': month_days[months[1]]}
                            ]
                        }
                    }
                },
                {
                    '$sort': {"avg_num": -1}
                }
            ]
            for col, result in enumerate(self.my_product.aggregate(pipeline, allowDiskUse=True)):
                sht1.set_row(col + 1, 50)
                for f_index, f in enumerate(result):
                    if f == '_id':
                        asin = result[f]
                        sht1.write(col + 1, 0, asin, body_style)
                    if f == 'root_cate':
                        sht1.write(col + 1, 2, result[f], body_style)
                    if f == 'sj_name':
                        sj_name = result[f]
                        sht1.write(col + 1, 3, sj_name, body_style)
                    if f == 'categories_tree':
                        sht1.write(col + 1, 4, result[f], body_style)
                    if f == 'group_name':
                        sht1.write(col + 1, 5, result[f], body_style)
                    if f == 'average_order':
                        if len(result[f]):
                            average_order = ''
                            for i in result[f]:
                                avg = i['order_num'] / month_days[i['month']]
                                avg_item = i['month'] + '日均单量：' + str(round(avg, 2))
                                average_order += avg_item + '|'
                            sht1.write(col + 1, 6, average_order, body_style)
                    if f == 'current':
                        if len(result[f]):
                            current = ''
                            for i in result[f]:
                                avg = i['order_num'] / month_days[i['month']]
                                current_item = i['month'] + '日均单量：' + str(round(avg, 2))
                                current += current_item + '|'
                            sht1.write(col + 1, 7, current, body_style)
                    if f == 'last':
                        if len(result[f]):
                            last = ''
                            for i in result[f]:
                                avg = i['order_num'] / month_days[i['month']]
                                last_item = i['month'] + '日均单量：' + str(round(avg, 2))
                                last += last_item + '|'
                            sht1.write(col + 1, 8, last, body_style)
                    if f == 'image':
                        sht1.write(col + 1, 1, result[f], body_style)
                        # # 网络图片URL
                        # image_url = modify_image_url(result[f])
                        # # 下载图片并保存到BytesIO对象中
                        # response = requests.get(image_url)
                        # image_data = BytesIO(response.content)
                        #
                        # # 写入数据和图片到Excel文件中
                        # sht1.insert_image(col + 1, 1, 'image.jpg', {'image_data': image_data, 'x_offset': 50})

            # 添加表头
            for index, h_item in enumerate(head):
                sht1.write(0, index, h_item, head_style)
        elif export_type == 'low_order':
            match = {}
            filter = self.remove_empty_elements(self.where)

            for i in filter:
                item = i.split('=')
                if not item[1] == '' and item[0]:
                    if item[0] == 'is_limit':
                        if int(item[1]):
                            match['limit_type'] = {"$nin": ['', 0]}
                        else:
                            match['limit_type'] = {"$in": ['', 0]}
                    elif item[0] == 'month':
                        month_three = item[1].split('|')
                        match['month'] = month_three[0]
                    elif item[0] == 'limit_type':
                        match['limit_type'] = int(item[1])
                    elif item[0] == 'month_num':
                        match['order_num'] = {'$lte': int(item[1])}
                    elif item[0] == 'country':
                        match['country_desc'] = str(item[1])
                    elif item[0] == 'status':
                        match['status'] = str(item[1])
                    elif item[0] == 'group_name':
                        match['group_name'] = item[1]
                    elif item[0] == 'asin':
                        match['asin'] = item[1].strip()

            head = ['ASIN', '国家', '在售状态', '所属组', '标题', 'Amazon分类树', month_three[0], month_three[1], month_three[2], '是否同步系统', '限制类型', '限制原因']
            sht1.set_column(0, len(head), 25)
            # print(match)
            # exit()

            for col, result in enumerate(self.low_order.find(match).sort('order_num', -1)):
                sht1.set_row(col + 1, 50)
                for f_index, f in enumerate(result):
                    value = result[f]

                    # 写入上月、上上月数据
                    sht1.write(col + 1, 7, result.get('last_order', 0), body_style)
                    sht1.write(col + 1, 8, result.get('last_last_order', 0), body_style)

                    if f == '_id':
                        continue
                    if f == 'asin':
                        sht1.write(col + 1, 0, value, body_style)
                    if f == 'country_desc':
                        sht1.write(col + 1, 1, value, body_style)
                    if f == 'status':
                        sht1.write(col + 1, 2, value, body_style)
                    if f == 'group_name':
                        sht1.write(col + 1, 3, value, body_style)
                    if f == 'title':
                        sht1.write(col + 1, 4, value, body_style)
                    if f == 'categories_tree':
                        sht1.write(col + 1, 5, value, body_style)
                    if f == 'order_num':
                        sht1.write(col + 1, 6, value, body_style)
                    if f == 'has_moved':
                        if value:
                            sht1.write(col + 1, 9, '已同步', body_style)
                        else:
                            sht1.write(col + 1, 9, '未同步', body_style)
                    if f == 'limit_type':
                        value = result[f]
                        if not value == '' and value is not None:
                            sht1.write(col + 1, 10, translate_limit_type(int(value)), body_style)
                    if f == 'limit_reason':
                        value = result[f]
                        if not value == '' and value is not None:
                            sht1.write(col + 1, 11, value, body_style)

            # 添加表头
            for index, h_item in enumerate(head):
                sht1.write(0, index, h_item, head_style)

        # 输出文件地址
        xls.close()
        self.myclient.close()
        path = 'https://api-analyse.wszzl.cn/' + path
        # path = 'http://local-xp.com/' + path
        print(path)

    def test(self):
        filter = self.remove_empty_elements(self.where)
        print(filter[2])


def translate_limit_type(limit_type):
    dis = {0: '不在售', 1: '侵权了', 2: '被投诉了', 3: '其他原因', 4: '其他原因2'}
    return dis.get(limit_type)

export = Export()
export.export_xls()
# export.test()
exit()
